package net.viralpatel.struts2;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JDBCQuery {

    private Connection conn;

    public JDBCQuery() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        try {			
	
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/t10", "root", "");
        System.out.println("connect ok");
    	} catch (Exception e) {
    		 System.out.println("not connect");
		}
    }
    
    public void updatePhi(int deposit,int cancel)throws SQLException
    {
    	String sql="UPDATE phi  SET deposit='"+deposit+"' ,cancel='"+cancel+"' WHERE khoachinh='10'";
        Statement stt = conn.createStatement();
        stt.executeUpdate(sql);
        
    }
    
    public void updateProfile(String username,String name,String email,String telephone,String company)throws SQLException
    {
    	String sql="UPDATE user  SET fullname='"+name+"' ,mail='"+email+"',tel='"+telephone+"',company='"+company+"' WHERE userID='"+username+"'";
        Statement stt = conn.createStatement();
        stt.executeUpdate(sql);
           	
    }
    
    public void updateRoomInfo(String roomId,int ghe,int price) throws SQLException
    {
    	String sql="UPDATE room  SET seat='"+ghe+"',price='"+price+"' WHERE roomId='"+roomId+"'";
        Statement stt = conn.createStatement();
        stt.executeUpdate(sql);       
    }
    
    public void deleteRoom(String roomID)throws SQLException
    {
    		String sql="UPDATE room SET status='0' Where roomID='"+roomID+"'";
    	    Statement stt = conn.createStatement();
            stt.executeUpdate(sql); 	
    }
    
    	public List<Phi> getPhi() throws SQLException
    	{
    		List<Phi> list = new ArrayList<Phi>();
    		String sql="Select * from phi WHERE khoachinh='10'";
    		 Statement stt = conn.createStatement();
    	        ResultSet res = stt.executeQuery(sql);
    	        while(res.next())
    	        {
    	        	int deposit=res.getInt("deposit");
    	        	int cancel=res.getInt("cancel");
    	        	
    	        	Phi ph=new Phi(deposit, cancel);
    	        	list.add(ph);		
    	        }
    		return list;
    	}
    
    public Connection getConnection(){
    	return conn;
    }
    public ResultSet getData(String sql) throws SQLException{
        Statement stt = conn.createStatement();
        ResultSet res = stt.executeQuery(sql);
        return res;
    }
    
    public ResultSet getRoom(String roomType) throws SQLException{
    	String sql="Select price,description from room where roomType2='"+roomType+"'";
        Statement stt = conn.createStatement();
        ResultSet res = stt.executeQuery(sql);
        return res;
    }
    
    public ResultSet getRoomID(String phong,String tang,String kaidan) throws SQLException
    {
    	String sql="Select roomID where roomType2='"+kaidan+"' AND floor='"+tang+"' AND roomNumber='"+phong+"'";
    	 Statement stt = conn.createStatement();
         ResultSet res = stt.executeQuery(sql);
         return res;
    	
    }

    public int updateData(String sql) throws SQLException{
        Statement stt = conn.createStatement();
        int num = stt.executeUpdate(sql);
        return num;
    }
    public int getDeposite(String roomId) throws SQLException	
    {
    	String sql="Select deposit from yoyaku where roomID="+roomId;
    	int deposit=0;
    	 Statement stt = conn.createStatement();
    	 ResultSet rs = stt.executeQuery(sql);
    	 while(rs.next())
 		{
 			deposit=rs.getInt("deposit");
 		}
    	return deposit;
    }
    
    public int getPrice(String roomtype2) throws SQLException
    {
    	String sql="select price from room where roomType2='"+roomtype2+"'";
    	int price=0;
   	 Statement stt = conn.createStatement();
   	 ResultSet rs = stt.executeQuery(sql);
   	 while(rs.next())
		{
			price=rs.getInt("price");
		}
   	return price;
    }
    
    
    public void insertData(String sql) throws SQLException
    {
    	Statement stt = conn.createStatement();
         stt.executeUpdate(sql);
    	stt.close();
    	conn.close();
    }
    /*
    public void updateYoyaku(String startTime,String endTime)
    {
    	String sql="update yoyaku set "
    	Statement stt = conn.createStatement();
       stt.executeUpdate(sql);
    	
    }
    */
public List<Time_T> getTime(String kaidan,String floor,String date)
{
String sql="Select startTime,endTime from yoyaku where day='"+date+"' AND roomID IN (Select roomID from room where floor='"+floor+"' AND roomType2='"+kaidan+"')";
	//String sql=" Select startTime,endTime from yoyaku where day='2012-03-08' AND roomID IN (Select roomID from room where floor='2' AND roomType2='D')";
	System.out.println("sql: "+sql);
	List<Time_T> list=new ArrayList<Time_T>();
	Statement stt;
	try {
		stt = conn.createStatement();
	    ResultSet rs = stt.executeQuery(sql);
	    while(rs.next())
		{
			String startTime=rs.getString("startTime");
			String endTime=rs.getString("endTime");
			Time_T time_t=new Time_T(startTime, endTime);
			list.add(time_t);
			
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return list;

}
public int getIndexYoyaku() throws SQLException// lay index cua bang yoyaku roi cong them 1 de xet no vao truong order id
{
	int index=-1;
	String sql="select MAX(stt) AS inx from yoyaku";
	 Statement stt = conn.createStatement();
	 ResultSet rs = stt.executeQuery(sql);
	 while(rs.next())
	{
		index=rs.getInt("inx");
	}
	return index;
}
public double getHour(String start,String end)
{
	double result=0;
	double nguyen=0;
	double le=0;
	String array_start[];	
	String array_end[];
	array_start=start.split(":");
	array_end=end.split(":");
	if(Integer.parseInt(array_start[1])>Integer.parseInt(array_end[1]))
	{
		le=-0.5;
		nguyen=Integer.parseInt(array_end[0])-Integer.parseInt(array_start[0]);
		result=nguyen+le;
	}
	else if(Integer.parseInt(array_start[1])<Integer.parseInt(array_end[1]))
	{
		le=0.5;
		nguyen=Integer.parseInt(array_end[0])-Integer.parseInt(array_start[0]);
		result=nguyen+le;
	}
	else
	{
		le=0;
		nguyen=Integer.parseInt(array_end[0])-Integer.parseInt(array_start[0]);
		result=nguyen+le;
		
	}
		
	return result;
}

public double setTime(String start,String end)
{
	double result=0;
	double nguyen=0;
	double le=0;
	String array_start[];
	
	String array_end[];
	array_start=start.split(":");
	array_end=end.split(":");
	if(Integer.parseInt(array_start[1])>Integer.parseInt(array_end[1]))
	{
		le=-0.5;
		nguyen=Integer.parseInt(array_end[1])-Integer.parseInt(array_start[1]);
		result=nguyen+le;
	}
	else if(Integer.parseInt(array_start[1])<Integer.parseInt(array_end[1]))
	{
		le=0.5;
		nguyen=Integer.parseInt(array_end[1])-Integer.parseInt(array_start[1]);
		result=nguyen+le;
	}
	else
	{
		le=0;
		nguyen=Integer.parseInt(array_end[1])-Integer.parseInt(array_start[1]);
		result=nguyen+le;
		
	}
		
	return result;
}

public List<Detail> getdetail(String roomtype) throws SQLException
{
	String sql="select price,description from room where roomType2='"+roomtype+"'";
	List<Detail> list=new ArrayList<Detail>();
	int price;
	String des;
	 Statement stt = conn.createStatement();
	 ResultSet rs = stt.executeQuery(sql);
	 while(rs.next())
	{
		price=rs.getInt("price");
		des=rs.getString("description");
		Detail detail=new Detail(price, des);
		list.add(detail);		
	}
	 return list;
}


public List<Yoyaku> getState(String ordercd) throws SQLException
{
	String sql="select orderID,yoyaku.roomID,day,startTime,endTime,cost,price,roomType2 from yoyaku,room where yoyaku.roomID=room.roomID";
	List<Yoyaku> list=new ArrayList<Yoyaku>();
	Statement stt=conn.createStatement();
	ResultSet rs=stt.executeQuery(sql);
	
	 String orderID;
	 String roomID;
	 String roomname;
	 String startTime;
	 String endTime;
	 String day;
	 int deposit;
	 int price;
	 int cost;
	 
	while(rs.next())
	{
		orderID=rs.getString("orderID");
		System.out.println("orderid"+orderID);
		roomID=rs.getString("roomID");
		roomname=rs.getString("roomType2");
		startTime=rs.getString("startTime");
		endTime=rs.getString("endTime");
		day=rs.getString("day");
		deposit=rs.getInt("deposit");
		price=rs.getInt("price");
		cost=rs.getInt("cost");		
		Yoyaku yo=new Yoyaku(orderID, roomID, roomname, startTime, endTime,day, price, cost);
		list.add(yo);
	}
	System.out.println("size:"+list.size());
	return list;
}


public List<Yoyaku> getYoyaku(String username) throws SQLException
{
	String sql="select orderID,yoyaku.roomID,day,startTime,endTime,cost,price,roomType2 from yoyaku,room where yoyaku.roomID=room.roomID AND flagCancel=0 AND userID='"+username+"'";
	List<Yoyaku> list=new ArrayList<Yoyaku>();
	Statement stt=conn.createStatement();
	ResultSet rs=stt.executeQuery(sql);
	
	 String orderID;
	 String roomID;
	 String roomname;
	 String startTime;
	 String endTime;
	 int deposit;
	 String day;
	 int price;
	 int cost;
	 
	while(rs.next())
	{
		orderID=rs.getString("orderID");
		roomID=rs.getString("roomID");
		roomname=rs.getString("roomType2");
		startTime=rs.getString("startTime");
		endTime=rs.getString("endTime");
		day=rs.getString("day");
		price=rs.getInt("price");
		cost=rs.getInt("cost");		
		Yoyaku yo=new Yoyaku(orderID, roomID, roomname, startTime, endTime, day, price, cost);
		list.add(yo);
	}
	return list;
}


/*------------delete yoyaku-----------------------------*/
public void deleteYoyaku(String ordercd) throws SQLException
{
	String sql=" UPDATE yoyaku SET flagCancel=1  WHERE orderID = '"+ordercd+"'";
	Statement stt=conn.createStatement();
	stt.executeUpdate(sql);
	conn.close();
}

    public void closeConnection() throws SQLException {
        conn.close();
    }
}
